package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import com.zr.model.Course;
import com.zr.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/** 
 * 提供课程设置的方法
 * @author JR
 *
 */
public class CourseDao {
	
	
	/** 
	 * 查询所有课程总数
	 * @return
	 */
	public int selectAllCouCount(){
		int ccount=0;
		String sql="select count(*) ccount from course";
		Connection con = JDBCUtil.getJDBConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				ccount = rs.getInt("ccount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		
		return ccount;
		
	}
	/** 
	 * 搜索课程的总数
	 * @param value 传入搜索关键字
	 * @return
	 */
	public int selectCouCount(String value){
		int ccount=0;
		String sql="select count(cid) ccount from course,teacher WHERE course.tid=teacher.tid  and (cname=? or tname =?)";
		Connection con = JDBCUtil.getJDBConnection();
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql);
			pst.setString(1, value);
			pst.setString(2, value);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				ccount = rs.getInt("ccount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		}
		
		return ccount;
		
	}
	/** 
	 * 提供查询所有课程的方法
	 * @return
	 */
	public List<Course> selectAllCourse(int start,int rows){
		List<Course> courses = new ArrayList<Course>();
		String Sql = "select cid,cname,cbtime,cetime,ccount,tname from course,teacher WHERE course.tid=teacher.tid limit ?,?";
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(Sql);
			pst.setInt(1,start);
			pst.setInt(2,rows);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Course course = new Course(rs.getInt("cid"), rs.getString("cname"), rs.getString("cbtime"), rs.getString("cetime"), rs.getString("ccount"), rs.getString("tname"));
				courses.add(course);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		
		
		return courses;
		
	}
	
}
	/** 
	 * 搜索课程的方法 
	 * @param start 
	 * @param rows
	 * @param value 传入搜索的关键字
	 * @return 返回课程
	 */
	public List<Course> selectCourse(String value){
		List<Course> courses = new ArrayList<Course>();
		String Sql = "select cid,cname,cbtime,cetime,ccount,tname from course,teacher WHERE course.tid=teacher.tid  and (cname like  ? or tname like ?)";
		Connection  con =  JDBCUtil.getJDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(Sql);
			pst.setString(1, "%"+value+"%");
			pst.setString(2, "%"+value+"%");
			
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Course course = new Course(rs.getInt("cid"), rs.getString("cname"), rs.getString("cbtime"), rs.getString("cetime"), rs.getString("ccount"), rs.getString("tname"));
				courses.add(course);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.closedConnection(con);
		
		
		return courses;
		
	}
	
}
	/** 
	 * 添加课程的方法
	 * @param course 传入需要添加的课程对象
	 * @return 成功添加返回true
	 */
		public boolean addCourse(Course course){
			
			boolean flag = false;
			String sql = "insert into course (cname,cbtime,cetime,ccount,tid) values (?,?,?,?,?)";
			Connection  con =  JDBCUtil.getJDBConnection();

			try {
				PreparedStatement pst = con.prepareStatement(sql);
				
				pst.setString(1,course.getCname());
				pst.setString(2,course.getCbtime());
				pst.setString(3,course.getCetime());
				pst.setInt(4, Integer.parseInt(course.getCcount()));
				pst.setInt(5, Integer.parseInt(course.getTname()));
				int rs = pst.executeUpdate();
				if(rs>0){
					flag = true;
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				JDBCUtil.closedConnection(con);
			
			return flag;
			
		}
}
		/** 
		 * 更改课程的方法
		 * @param course 传入需要添加的课程对象
		 * @return 成功更改返回true
		 */
			public boolean updateCourse(Course course){
				TeacherDao td = new TeacherDao();
				boolean flag = false;
				String sql = "update course set cname=?,cbtime=?,cetime=?,ccount=?,tid=? where cid=?";
				Connection  con =  JDBCUtil.getJDBConnection();

				try {
					PreparedStatement pst = con.prepareStatement(sql);
					
					pst.setString(1,course.getCname());
					pst.setString(2,course.getCbtime());
					pst.setString(3,course.getCetime());
					pst.setInt(4, Integer.parseInt(course.getCcount()));
					pst.setInt(5, td.findTeacherId(course.getTname()));
					
					pst.setInt(6, course.getCid());
					int rs = pst.executeUpdate();
					if(rs>0){
						flag = true;
					}
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				
				return flag;
				
			}
	}
			
			/** 
			 * 删除课程的方法 
			 * @param cid 传入需要删除课程的ID
			 * @return 删除成功返回true 失败返回false
			 */
			public boolean delCourse(int cid){
				
				boolean flag = false;
				String sql = "delete from course where cid = ?";
				Connection  con =  JDBCUtil.getJDBConnection();

				try {
					PreparedStatement pst = con.prepareStatement(sql);
					
					pst.setInt(1,cid);
					
					int rs = pst.executeUpdate();
					if(rs>0){
						flag = true;
					}
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				
				return flag;
				
			}
	}
			
			/** 
			 * 提供设置选课时间的方法
			 * @param cbtime 传入开始选课的时间
			 * @param cetime 传入结束选课的时间
			 * @return 设置成功返回true
			 */
			public boolean setCourseSelectTime(String cbtime,String cetime){
				
				boolean flag = false;
				String sql = "update  time set cbtime=?,cetime=? where timeid=1";
				Connection  con =  JDBCUtil.getJDBConnection();

				try {
					PreparedStatement pst = con.prepareStatement(sql);
					
					pst.setString(1,cbtime);
					pst.setString(2, cetime);
					
					int rs = pst.executeUpdate();
					if(rs>0){
						flag = true;
					}
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				
				return flag;
				
			}
	}
			/** 
			 * 查询当前设置的选课时间
			 * @return
			 */
			public JSONObject getCourseSelectTime(){
				JSONObject jso = new JSONObject();
				
				String sql = "select cbtime,cetime from time where timeid = 1";
			
				
				Connection  con =  JDBCUtil.getJDBConnection();
				try {
					PreparedStatement pst = con.prepareStatement(sql);
					ResultSet rs = pst.executeQuery();
					while(rs.next()){
						jso.put("cbtime", rs.getString("cbtime"));
						jso.put("cetime", rs.getString("cetime"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return jso;
				
			
			}
			

			/** 
			 * 提供设置成绩录入时间的方法
			 * @param cbtime 传入开始录入的时间
			 * @param cetime 传入结束录入的时间
			 * @return 设置成功返回true
			 */
			public boolean setAddMarkTime(String cbtime,String cetime){
				
				boolean flag = false;
				String sql = "update  time set cbtime=?,cetime=? where timeid=2";
				Connection  con =  JDBCUtil.getJDBConnection();

				try {
					PreparedStatement pst = con.prepareStatement(sql);
					
					pst.setString(1,cbtime);
					pst.setString(2, cetime);
					
					int rs = pst.executeUpdate();
					if(rs>0){
						flag = true;
					}
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					JDBCUtil.closedConnection(con);
				
				return flag;
				
			}
	}
			
			/** 
			 * 查询当前设置的成绩录入时间
			 * @return
			 */
			public JSONObject getAddMarkTime(){
				JSONObject jso = new JSONObject();
				
				String sql = "select cbtime,cetime from time where timeid = 2";
			
				
				Connection  con =  JDBCUtil.getJDBConnection();
				try {
					PreparedStatement pst = con.prepareStatement(sql);
					ResultSet rs = pst.executeQuery();
					while(rs.next()){
						jso.put("cbtime", rs.getString("cbtime"));
						jso.put("cetime", rs.getString("cetime"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return jso;
				
			
			}
			/** 
			 * 判断当前时间是否在录入成绩设置时间内
			 * @return 在就返回true
			 */
			public boolean compareAddMarkTime(){
				boolean flag = false;
				//获取为日期和时间使用 MEDIUM 风格的默认日期/时间格式器。
				DateFormat df = DateFormat.getDateInstance(DateFormat.MEDIUM);
			
				 JSONObject date = this.getAddMarkTime();
				 String cbtime = date.getString("cbtime");
				 String cetime = date.getString("cetime");
				 
				 
				 Date now = Calendar.getInstance().getTime();
				 
				 
				 
				 try {
					 //获取录入成绩的开始时间
					 Date cbTime = df.parse(cbtime.toString());
					 //获取录入成绩的结束时间
					 Date ceTime = df.parse(cetime.toString());
					
					 
					
					
					if(now.after(cbTime)&&now.before(ceTime)){
						flag=true;
					}
					
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
				return flag;
				
				
				
			}
			/**判断当前时间是否在选课设置时间内
			 * 
			 * @return
			 */
			public boolean compareCourseSelectTime(){
				boolean flag = false;
				//获取为日期和时间使用 MEDIUM 风格的默认日期/时间格式器。
				DateFormat df = DateFormat.getDateInstance(DateFormat.MEDIUM);
			
				 JSONObject date = this.getCourseSelectTime();
				 String cbtime = date.getString("cbtime");
				 String cetime = date.getString("cetime");
				 
				 
				 Date now = Calendar.getInstance().getTime();
				 
				 
				 
				 try {
					 //获取录入成绩的开始时间
					 Date cbTime = df.parse(cbtime.toString());
					 //获取录入成绩的结束时间
					 Date ceTime = df.parse(cetime.toString());
					
					 
					
					
					if(now.after(cbTime)&&now.before(ceTime)){
						flag=true;
					}
					
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
				return flag;
				
				
				
			}
}
